# coding:utf-8
import re
from typing import Union

from django.db.models import Q
import xlrd, time, datetime, calendar
import hashlib

from django.http import HttpResponse
from django.urls import resolve
from django.utils.encoding import smart_str
from django.utils.http import urlquote
from xlsxwriter.workbook import Workbook


def dict_type_to_str(data):
    """字典中的时间格式转成字符串"""
    for key, value in data.items():
        if isinstance(value, datetime.datetime):
            data[key] = time_trans_str_s(value)
        elif isinstance(value, datetime.date):
            data[key] = data[key].strftime("%Y-%m-%d") if data[key] else ''
    return data


def del_dict_key(data, keys_to_remove):
    for key in keys_to_remove:
        data.pop(key, None)
    return data


def find_empty_keys(d, keys):
    """
    检查字典中指定的多个 key 的值是否为空，并返回所有值为空的 key。

    空的定义包括：None, 空字符串 '', 空列表 [], 空字典 {}。
    """

    # 自定义判空逻辑
    def is_empty(value):
        return value in (None, "", [], {})

    # 找出值为空的 key
    empty_keys = [key for key in keys if is_empty(d.get(key))]
    return empty_keys


def dictfetchall(cursor):
    """
    将cursor.fetchall()结果转化为字典列表
    """
    columns = [col[0] for col in cursor.description]
    return [dict(zip(columns, row)) for row in cursor.fetchall()]


def re_int(txt):
    """取数字"""
    if not txt:
        return None
    patt = re.compile(r"\d+", re.I)
    res = re.findall(patt, txt)
    if res:
        return res[-1]
    else:
        return None


def get_models_fields(data, model):
    """获取表字段"""
    # 获取模型的字段列表
    fields = model._meta.fields

    # 遍历字段列表并输出字段名
    tmp_list = []
    for field in fields:
        if field.name != 'id':
            tmp_list.append(field.name)
    info = {}
    for k, v in data.items():
        if k in tmp_list:
            info[k] = v
    return info


def common_export(info, name):
    """通用导出设置"""

    filename = urlquote(smart_str(str(name) + '_' + time.strftime('%Y%m%d', time.localtime(time.time())) + '.xlsx'))
    print(filename)
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename=' + filename
    book = Workbook(response, {'in_memory': True})
    sheet = book.add_worksheet('sheet0')

    sheet.set_column("A:Z", 15)
    row = 0
    for i in info:
        sheet.write_row(row, 0, i)
        row += 1

    book.close()
    return response


def common_order_export(info, name):
    """通用导出设置，支持'操作类型'字段的下拉选项"""
    import time
    from django.http import HttpResponse
    from django.utils.http import urlquote
    from django.utils.encoding import smart_str
    from openpyxl import Workbook
    from openpyxl.worksheet.datavalidation import DataValidation

    filename = urlquote(smart_str(f"{name}_{time.strftime('%Y%m%d', time.localtime())}.xlsx"))
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = f'attachment; filename={filename}'

    # 创建 Excel 工作簿
    book = Workbook()
    sheet = book.active
    sheet.title = "sheet0"

    # 设置列宽
    for col in range(1, 27):  # A-Z列
        sheet.column_dimensions[chr(64 + col)].width = 15

    # 头部信息写入
    for row_idx, row_data in enumerate(info, start=1):
        for col_idx, cell_value in enumerate(row_data, start=1):
            sheet.cell(row=row_idx, column=col_idx, value=cell_value)

    # 定义下拉选项
    dropdown_options = ["上架", "下架", "变更"]
    dv = DataValidation(type="list", formula1=f'"{",".join(dropdown_options)}"', showDropDown=True)
    dv.error = "请选择一个有效的操作类型"
    dv.errorTitle = "输入错误"
    dv.prompt = "请选择一个操作类型"
    dv.promptTitle = "提示"

    # 获取“操作类型”列索引（Excel 第3列，对应 B 列）
    dropdown_col = 3  # Excel 列索引从 1 开始
    sheet.add_data_validation(dv)

    # 绑定数据验证到 "操作类型" 列，从第二行（数据区域）开始
    max_rows = 500  # 假设最多支持 500 行数据
    for row in range(2, max_rows + 1):
        dv.add(sheet.cell(row=row, column=dropdown_col))

    # 保存 Excel 到 response
    from io import BytesIO
    output = BytesIO()
    book.save(output)
    response.write(output.getvalue())

    return response


def list_diff_opt(list1, list2):
    """找出列表1不存在于列表2 的元素"""
    set1 = set(list1)
    set2 = set(list2)

    difference = list(set1.difference(set2))
    return difference


def list_diff_dict_opt(list1, list2):
    """找出字典列表1不存在于字典列表2 的元素"""
    difference = [item for item in list1 if item not in list2]
    return difference


def list_dict_deduplication(list_of_dicts):
    unique_dicts = []
    for d in list_of_dicts:
        if d not in unique_dicts:
            unique_dicts.append(d)
    return unique_dicts


def get_account(username):
    """获取订单的申请人域账号"""
    if username:
        t = str(username).split('[')
        new_name = t[-1].replace(']', '')
    else:
        new_name = ''
    return new_name

def get_rand_str():
    a = datetime.datetime.now()
    b = str(a).split('.')
    return b[-1]


def int_to_str(data):
    tmp_list = []
    for i in data:
        tmp_list.append(str(i))
    return tmp_list


def str_to_int(data):
    tmp_list = []
    for i in data:
        tmp_list.append(int(i))
    return tmp_list


def time_stamp(timeNum):
    timeStamp = float(timeNum/1000)
    timeArray = time.localtime(timeStamp)
    otherStyleTime = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)
    return otherStyleTime


def excel_to_str_time(cell):
    """
    excel表中时间戳转datetime
    :param cell:
    :return:
    """
    tmp = xlrd.xldate_as_tuple(cell, 0)
    # date = datetime(tmp)
    tmp = tmp + (0, 0, 0)
    # cell = int(time.mktime(tmp))
    cell = time.strftime('%Y-%m-%d %H:%M:%S', tmp)
    # cell = cell + '.000000'
    cell = datetime.datetime.strptime(cell, "%Y-%m-%d %H:%M:%S")
    return cell


def unrepeat(data):
    info = []
    for i in data:
        if i not in info:
            info.append(i)
    return info


def dict_sort(data, reverse, key):
    """
    字典按键值排序
    :param data:
    :return:
    """
    info = []
    if not data:
        return []
    for i in data:
        info = sorted(data, key=lambda x: x[key], reverse=reverse)
    return info


def dict_to_list_sort(data, name, value, reverse=False):
    """字典变key列表与value列表"""
    key_list = []
    val_list = []
    for item in data:
        key_list.append(item[name])
        val_list.append(item[value])
    if reverse:
        return list(reversed(key_list)), list(reversed(val_list))
    else:
        return key_list, val_list


def get_ip(request):
    """
    获取请求者的IP信息
    :param request:
    :return:
    """
    ip = ''
    if 'HTTP_X_FORWARDED_FOR' in request.META:
        x_forwarded_for = request.META.get('HTTP_X_FORWARDED_FOR')  # 判断是否使用代理
        if x_forwarded_for:
            ip = x_forwarded_for.split(',')[0]  # 使用代理获取真实的ip
    else:
        ip = request.META.get('REMOTE_ADDR')  # 未使用代理获取IP
    return ip


def log_record(request, content):
    """
    日志记录函数
    :param request:
    :return:
    """
    username = request.session.get('username')
    path = request.get_full_path()
    rm = resolve(request.path)
    path_name = rm.url_name
    alia_name = request.session.get('cnname')
    source_host = get_ip(request)
    # if path_name:
    # OptLog.objects.create(name=username, alias_name=alia_name, source_host=source_host, full_url=path, desc=path_name, content=content).save()


def api_log(desc, content):
    """接口日志记录"""
    # OptLog.objects.create(desc=desc, content=content).save()


def get_cur_year_months():
    # 获取当前年份
    now = datetime.datetime.now()
    current_year = now.year
    current_month = now.month

    # 生成从年初到当前月的所有月份的列表
    months = [datetime.datetime(current_year, month, 1).strftime('%Y-%m') for month in range(1, current_month + 1)]

    # 输出结果
    all_month = []
    for month in months:
        all_month.append(month)
    return all_month


def get_days_in_month(year_month):
    # 解析传入的年月
    tmp = year_month.split('-')
    if len(tmp) > 2:
        del tmp[-1]
    year, month = map(int, tmp)

    # 获取当月的天数
    _, num_days = calendar.monthrange(year, month)

    # 生成当月所有天数的列表
    days = [datetime.datetime(year, month, day).strftime('%Y-%m-%d') for day in range(1, num_days + 1)]

    return days


def cal_month_range(dot, begin, end):
    """
    计算日期区间内的所有月份，dot为'.'和'-'，因为上传的表格格式不一样，处理也不一样
    :param dot:
    :param begin:
    :param end:
    :return:
    """
    if ' ' in begin:
        tmp = begin.split()[0].split(dot)
        begin = tmp[0] + '-' + tmp[1]
        tmp = end.split()[0].split(dot)
        end = tmp[0] + '-' + tmp[1]
    month_range = [begin]
    while not begin == end:
        begin = begin.split(dot)
        if begin[1] == '12':
            begin[0] = str(int(begin[0])+1)
            if dot == '-':
                begin[1] = '01'
            else:
                begin[1] = '1'
        else:
            month = int(begin[1])+1
            if month < 10 and dot == '-':
                begin[1] = '0'+str(month)
            else:
                begin[1] = str(month)
        begin = dot.join(begin)
        month_range.append(begin)
    return month_range


def get_work_order_nums(order_type):
    """
    获取工单号
    :param order_type:
    :return:
    """
    now = datetime.datetime.now()
    d = str(now)
    tmp1 = d.split('.')[0]
    tmp2 = tmp1.split()
    tmp3 = tmp2[0].split('-')
    tmp3 = ''.join(tmp3)
    tmp4 = tmp2[1].split(':')
    tmp4 = ''.join(tmp4)
    work_order_nums = order_type + tmp3 + tmp4
    return work_order_nums


def get_cur_day(date):
    if not date:
        return False
    tmp = str(date).split()
    day_s = tmp[0] + ' 00:00:00'
    day_e = tmp[0] + ' 23:59:59'
    return str_to_datetime(day_s), str_to_datetime(day_e)


def get_cur_year(date):
    if not date:
        return False
    tmp_year = date.split()[0].split('-')[0]
    year_s = tmp_year + '-01-01 00:00:00'
    year_e = tmp_year + '-12-31 23:59:59'
    return str_to_datetime(year_s), str_to_datetime(year_e)


def has_feb_29(year_str: str) -> bool:
    try:
        year = int(year_str)
        return calendar.isleap(year)
    except ValueError:
        raise ValueError("请输入有效的年份字符串，例如 '2024'")


def get_cur_month(date):
    tmp_month = date.split()[0].split('-')[1]
    tmp_year = date.split()[0].split('-')[0]
    if str(tmp_month) in ['01', '03', '05', '07', '08', '10', '12', '1', '3', '5', '7', '8']:
        s_start = str(tmp_year) + '-' + str(tmp_month) + '-' + '01'
        s_end = str(tmp_year) + '-' + str(tmp_month) + '-' + '31'
    elif str(tmp_month) in ['04', '06', '09', '11', '4', '6', '9']:
        s_start = str(tmp_year) + '-' + str(tmp_month) + '-' + '01'
        s_end = str(tmp_year) + '-' + str(tmp_month) + '-' + '30'
    else:
        s_start = str(tmp_year) + '-' + str(tmp_month) + '-' + '01'
        if has_feb_29(tmp_year):
            s_end = str(tmp_year) + '-' + str(tmp_month) + '-' + '29'
        else:
            s_end = str(tmp_year) + '-' + str(tmp_month) + '-' + '28'

    s_start = s_start + ' 00:00:00'
    s_end = s_end + ' 23:59:00'
    s_start = str_to_datetime(s_start)
    s_end = str_to_datetime(s_end)
    return s_start, s_end


def get_cur_month_str(date):
    # now = datetime.datetime.now()
    # tmp = time_trans_str(now)
    c_start, c_end = get_cur_month(date)
    return time_trans_str_s(c_start), time_trans_str_s(c_end)


def get_last_month(date):
    tmp_month = date.split()[0].split('-')[1]
    tmp_year = date.split()[0].split('-')[0]
    if int(tmp_month) == 1:
        s_start = str(int(tmp_year) - 1) + '-' + '12' + '-' + '01'
        s_end = str(int(tmp_year) - 1) + '-' + '12' + '-' + '31'
    else:
        if str(int(tmp_month) - 1) in ['1', '3', '5', '7', '8', '10', '12']:
            s_start = str(tmp_year) + '-' + str(int(tmp_month) - 1) + '-' + '01'
            s_end = str(tmp_year) + '-' + str(int(tmp_month) - 1) + '-' + '31'
        elif str(int(tmp_month) - 1) in ['4', '6', '9', '11']:
            s_start = str(tmp_year) + '-' + str(int(tmp_month) - 1) + '-' + '01'
            s_end = str(tmp_year) + '-' + str(int(tmp_month) - 1) + '-' + '30'
        else:
            s_start = str(tmp_year) + '-' + str(int(tmp_month) - 1) + '-' + '01'
            s_end = str(tmp_year) + '-' + str(int(tmp_month) - 1) + '-' + '28'
    s_start = s_start + ' 00:00:00'
    s_end = s_end + ' 23:59:00'
    s_start = str_to_datetime(s_start)
    s_end = str_to_datetime(s_end)
    return s_start, s_end


def get_last_last_month(date):
    tmp_month = date.split()[0].split('-')[1]
    tmp_year = date.split()[0].split('-')[0]
    if int(tmp_month) == 1:
        s_start = str(int(tmp_year) - 1) + '-' + '11' + '-' + '01'
        s_end = str(int(tmp_year) - 1) + '-' + '11' + '-' + '30'
    elif int(tmp_month) == 2:
        s_start = str(int(tmp_year) - 1) + '-' + '12' + '-' + '01'
        s_end = str(int(tmp_year) - 1) + '-' + '12' + '-' + '31'
    else:
        s_start = str(tmp_year) + '-' + str(int(tmp_month) - 2) + '-' + '01'
        s_end = str(tmp_year) + '-' + str(int(tmp_month) - 2) + '-' + '28'
    s_start = s_start + ' 00:00:00'
    s_end = s_end + ' 23:59:00'
    s_start = str_to_datetime(s_start)
    s_end = str_to_datetime(s_end)
    return s_start, s_end


def md5(str2):
    m = hashlib.md5()
    m.update(str2.encode("utf8"))
    return m.hexdigest()


def time_trans_str_s(a):
    if not a:
        return ""
    if '.' in str(a):
        tmp = str(a).split('.')
        del tmp[-1]
        tmp = '.'.join(tmp)
    else:
        tmp = str(a)
    return tmp


def utc_time_trans(a):
    if a:
        b = a.split('T')[0]
        c = str_to_datetime(b)
        return c
    else:
        return None


def time_trans(a):
    tmp = str(a).split('.')
    del tmp[-1]
    tmp2 = tmp[0].split()
    del tmp2[-1]
    tmp = '.'.join(tmp2)
    return tmp


def time_trans_str(a):
    if '.' in str(a):
        tmp = time_trans(a)
        return tmp
    else:
        tmp = str(a).split()
        del tmp[-1]
        tmp = '.'.join(tmp)
        return tmp


def str_to_datetime(a, end=False, default=None):
    if a:
        if isinstance(a, datetime.datetime):
            return a

        if ' ' not in a:
            if end:
                a = a + ' 23:59:59'
            else:
                a = a + ' 00:00:00'

        if '-' in a:
            fmt = "%Y-%m-%d %H:%M:%S"
        elif '/' in a:
            fmt = "%Y/%m/%d %H:%M:%S"
        else:
            return None

        tmp = datetime.datetime.strptime(a, fmt)
        return tmp

    elif default:
        return default
    else:
        return None


def str_to_month_datetime(a, end=False):
    if a:
        if ' ' not in a:
            if not end:
                a = a + '-01 00:00:00'
            else:
                a = a + '-28 00:00:00'
        else:
            a = a
        tmp = datetime.datetime.strptime(a, "%Y-%m-%d %H:%M:%S")
        return tmp
    else:
        return None


def get_diff_minute(start_time, end_time):
    if type(start_time) is str:
        start_time = str_to_datetime(start_time)
    if type(end_time) is str:
        end_time = str_to_datetime(end_time)
    diff = end_time - start_time
    minute = round(diff.total_seconds() / 60, 2)
    return minute


def dict_key_total(my_list, key_name):
    """统计字典内指定键值总和"""
    specified_key = key_name

    # 初始总和
    total_sum = 0

    # 循环迭代列表，并累加指定键的值
    for my_dict in my_list:
        if specified_key in my_dict:
            total_sum += my_dict[specified_key]
    return total_sum


def search_list(search_value_dict):
    """
    提供批量模糊搜索功能
    :param search_value_dict:
    :return: (AND: ('grp_id', 29L), (OR: ('id', 21), ('id', 2)))
    """
    all_query = Q()
    for i in search_value_dict:
        query = None
        #for value in search_value_dict[i]:
        q = Q(**{"%s__icontains" % i: search_value_dict[i]})
        if not query:
            query = q
        else:
            query = Union[query, q]
        all_query = all_query & query
    return all_query


def excel_format(path):
    """
    excel数据格式化
    :param path:
    :return:
    """
    wb = xlrd.open_workbook(filename=path)
    sheet = wb.sheet_by_index(0)
    rows = sheet.nrows
    cols = sheet.ncols
    all_content = []
    for i in range(rows):
        row_content = []
        for j in range(cols):
            ctype = sheet.cell(i, j).ctype  # 表格的数据类型
            cell = sheet.cell_value(i, j)
            # if ctype == 2 and cell % 1 == 0:  # 如果是整形
            #    cell = int(cell)
            if ctype == 3:
                # 转成datetime对象
                tmp = xlrd.xldate_as_tuple(cell, 0)
                # date = datetime(tmp)
                tmp = tmp + (0, 0, 0)
                # cell = int(time.mktime(tmp))
                cell = time.strftime('%Y-%m-%d %H:%M:%S', tmp)
                # cell = cell + '.000000'
                cell = datetime.datetime.strptime(cell, "%Y-%m-%d %H:%M:%S")
                # cell = cell.split()[0]
                # cell= cell.date()
            elif ctype == 4:
                cell = True if cell == 1 else False
            row_content.append(cell)
        all_content.append(row_content)
    return all_content


def dict_unrepeat(data, key):
    """
    字典按键值去重
    :return:
    """
    l4 = []
    l4.append(data[0])
    for dict in data:
        # print len(l4)
        k = 0
        for item in l4:
            # print 'item'
            if dict[key] != item[key]:
                k = k + 1
                # continue
            else:
                break
            if k == len(l4):
                l4.append(dict)
    return l4


def add_years(dt, n):
    """
    年份加减
    """
    year = dt.year + n
    month = dt.month
    day = min(dt.day, calendar.monthrange(year, month)[1])

    return dt.replace(year=year, month=month, day=day)

